﻿"""
判定加完系数的测试数据与标准件是否相符，相符修改CF2 CF3并下发湿胶或者成品坐标点
"""
import pyodbc
import os
import shutil
from datetime import datetime
import pandas as pd
from sqlalchemy import create_engine
from sortingdataprocess import get_standpart_data, get_test_data, cal_coef, cal_end_coef, get_cf, get_item_code, \
    decrease_data
from getcobspec import *
from setiniconfig import *


def config_mp(form3_infos, pre_path, cnxn, conn, engine, logger):
    engine2 = create_engine(
        "mssql+pyodbc://qatest:qatest@192.168.3.236:1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server",
        fast_executemany=True)
    # 创建游标
    cursor = cnxn.cursor()
    cursor.fast_executemany = True
    # 创建游标
    cursor1 = conn.cursor()
    cursor1.fast_executemany = True
    pd.set_option('display.max_columns', None)

    flash_result = {}
    endconf_path = None
    lotID3 = None
    mpstate = False

    item_code = get_item_code(form3_infos['MoID'])
    df_color = get_color_stand_2(form3_infos['MoID'])
    flag2 = False
    if not df_color.empty:
        for i in df_color.itertuples():
            for column in ['CCT_CODE','CIEx', 'CIEy', 'a', 'b', 'θ']:
                # 判断颜色标准是否含有空值
                if getattr(i, column) is None or pd.isna(getattr(i, column)):
                    flag2 = True
    if item_code:
        CF2_pars = get_cf(item_code, 2)
        CF3_pars = get_cf(item_code, 3)
        standpart_data = get_standpart_data(item_code)
    else:
        CF2_pars = pd.DataFrame()
        CF3_pars = pd.DataFrame()
        standpart_data = pd.DataFrame()
    test_data = get_test_data(form3_infos['temp_path'], form3_infos['MachineID'], logger)
    len1 = len(test_data.index)

    if CF2_pars.empty or CF3_pars.empty or standpart_data.empty or test_data.empty:
        flash_result[0] = f"CF2为{not CF2_pars.empty}，CF3为{not CF3_pars.empty}，" \
                          f"标准件数据为{not standpart_data.empty}，测试数据为{not test_data.empty}"
        logger.warn(f"{form3_infos['MachineID']}号设备,CF2为{not CF2_pars.empty}，CF3为{not CF3_pars.empty}，"
                    f"标准件数据为{not standpart_data.empty}，测试数据为{not test_data.empty}")
    elif form3_infos['MoID'].startswith('5101-') and len1 < 3:
        logger.warn(f"{form3_infos['MachineID']}号设备，{form3_infos['MoID']}工单--测试数据少于3条!")
        flash_result[1] = "请使用3个或3个以上标准灯进行测试！！"
    elif get_spec_range(item_code).query("Parameter == 'ForwardVoltage_V'").empty:
        flash_result[2] = "SpecRange表格无有效电压范围数据,找研发负责人"
        logger.warn(f"{form3_infos['MachineID']}号设备,SpecRange表格无有效电压范围数据")
    elif get_color_stand(item_code).empty:
        flash_result[3] = "Color_Standard表格无有效颜色标准数据,找研发负责人"
        logger.warn(f"{form3_infos['MachineID']}号设备,Color_Standard表格无有效颜色标准数据")
    else:
        # test_data删除电流为NA和0，去除Seclect列
        test_data = test_data[test_data['Current_mA'] != 0]
        test_data.dropna(subset=['Current_mA'], inplace=True)
        test_data.drop(columns=['Select'], axis=1, inplace=True)

        compare_data = cal_coef(standpart_data, test_data)
        if type(compare_data) == pd.DataFrame and compare_data.empty:
            flash_result[10] = "测试数据与标准数据编码不对应"
            logger.warn(f"{form3_infos['MachineID']}号设备,测试数据与标准数据编码不对应")
        elif type(compare_data) != pd.DataFrame and not compare_data:
            logger.warn(f"{form3_infos['MachineID']}号设备，{form3_infos['MoID']}工单--测试数据无效!")
            flash_result[2] = "机台启动失败,光谱图数据无效，请发起设备维修申请"
        else:
            compare_data.drop(index='校准系数', inplace=True)
            compare_data['dlm'] = compare_data['dlm'].apply(lambda x: x - 1)
            # print(f'比对DataFrame:\n{compare_data}')
            total_counts = len(compare_data)
            # 每个灯进行判定，不合格数据去除
            # 去除列表
            remove_list = []
            for index in range(total_counts):
                if abs(compare_data.iloc[index]['dx']) > 0.0015 or abs(compare_data.iloc[index]['dy']) > 0.0015 \
                        or abs(compare_data.iloc[index]['dRa']) > 2 or abs(compare_data.iloc[index]['dR9']) > 2 \
                        or abs(compare_data.iloc[index]['dlm']) > 0.03:
                    remove_list.append(index)
            remove_list = list(set(remove_list))
            # print(remove_list)
            cal_requirments = {1: 1, 2: 1, 3: 2, 4: 2, 5: 3, 6: 3, 7: 4, 8: 5, 9: 6, 10: 6}
            if total_counts not in cal_requirments.keys():
                flash_result[20] = "测试数据小于1或者大于10"
                logger.warn(f"{form3_infos['MachineID']}号设备,测试数据小于1或者大于10")
            # 校准失败数量超标记得使用to_html转为表格
            elif (total_counts in cal_requirments) and (
                    (total_counts - len(remove_list)) < cal_requirments[total_counts]):
                flash_result['校准异常'] = compare_data.iloc[remove_list]
            else:
                # 生成删除数据的列表
                NO_list = []
                for i in remove_list:
                    NO_list.append(int(compare_data.iloc[i]['StandardPartNO']))
                # 删除不合格，并重新计算校准系数
                # 获取服务器存储的校准数据，进行重新计算
                test_data2 = get_test_data(pre_path, form3_infos['MachineID'], logger)
                test_data2 = test_data2[test_data2['Current_mA'] != 0]
                test_data2.dropna(subset=['Current_mA'], inplace=True)
                test_data2.drop(columns=['Select'], axis=1, inplace=True)

                for i in remove_list:
                    if i not in list(test_data2.index):
                        remove_list.remove(i)
                test_data2 = test_data2.drop(index=remove_list)
                # 新的对比DataFrame
                new_compare_data = cal_coef(standpart_data, test_data2)

                # 重新赋值，替换原有的值进行最终系数的计算
                form3_infos['coef_data'] = new_compare_data.loc['校准系数'].to_dict()

                # 计算并保存差值
                save_data = decrease_data(standpart_data, test_data, form3_infos['MoID'])
                save_data.to_sql('Compare_Data', con=engine2, if_exists='append', index=None)
                # 创建上传与下载目录
                uploadsDir = os.path.join('uploads', 'caldata', str(datetime.now().year), str(datetime.now().month))
                if not os.path.exists(uploadsDir):
                    os.makedirs(uploadsDir)
                # 使用 年+月+机器号的方式存储最后一步配置文件
                downloadsDir = os.path.join('downloads', 'endcoef', str(datetime.now().year), str(datetime.now().month),
                                            str(form3_infos['MachineID']))
                if not os.path.exists(downloadsDir):
                    os.makedirs(downloadsDir)
                # 计算最终系数
                end_coef = cal_end_coef(form3_infos['coef_data'], CF2_pars, CF3_pars)
                ini_file_read(form3_infos['CoePath'])
                # 小批量湿胶
                if form3_infos['ProcessID'] == '1':
                    if get_smallbatch_wet(form3_infos['MoID']).empty:
                        flash_result[30] = "小批量视图Smallbatch_initial_Ratio无有效数据"
                        logger.warn(f"{form3_infos['MachineID']}号设备,小批量视图Smallbatch_initial_Ratio无有效数据")
                    else:
                        # 获取点粉电流，电压再上限的基础上再加5V
                        df_volt = get_spec_range(item_code)
                        voltage = (df_volt[df_volt['Parameter'] == 'ForwardVoltage_V']['USL'].to_list()[0]) + 5
                        current = get_item_features(item_code, '点粉电流(mA)').loc[0, 'FEATURE_VALUE']
                        if not current:
                            flash_result[40] = "视图ITEM_CODE_VALUE无点粉电流有效数据,找研发负责人"
                            logger.warn(f"{form3_infos['MachineID']}号设备,视图ITEM_CODE_VALUE无点粉电流有效数据")
                        else:
                            set_voltage_current(voltage, current)
                            # 设置最终系数
                            set_cal_factor(end_coef.loc['最终系数'].to_dict())
                            # 设置中心点
                            set_wet_xy(get_color_stand(item_code), get_smallbatch_wet(form3_infos['MoID']), '1')
                            # 设置色容差
                            set_mac_bin(get_bin_range(item_code), get_spec_range(item_code), form3_infos['ProcessID'])
                            mpstate = True
                # 量产湿胶
                elif form3_infos['ProcessID'] == '2':
                    if get_mp_wet(form3_infos['MoID']).empty:
                        flash_result[40] = "量产湿胶视图Smallbatch_perfect无数据"
                        logger.warn(f"{form3_infos['MachineID']}号设备,量产湿胶视图Smallbatch_perfect无数据")
                    elif pd.isna(get_mp_wet(form3_infos['MoID']).loc[0, 'Adjust_CIEx']):
                        flash_result[41] = "量产湿胶视图Smallbatch_perfect无Adjust_CIEx有效数据"
                        logger.warn(f"{form3_infos['MachineID']}号设备,量产湿胶视图Smallbatch_perfect无Adjust_CIEx有效数据")
                    elif pd.isna(get_mp_wet(form3_infos['MoID']).loc[0, 'Adjust_CIEy']):
                        flash_result[41] = "量产湿胶视图Smallbatch_perfect无Adjust_CIEx有效数据"
                        logger.warn(f"{form3_infos['MachineID']}号设备,量产湿胶视图Smallbatch_perfect无Adjust_CIEy有效数据")
                    elif pd.isna(get_mp_wet(form3_infos['MoID']).loc[0, 'Wet_Glue_lm']):
                        flash_result[41] = "量产湿胶视图Smallbatch_perfect无Wet_Glue_lm有效数据"
                        logger.warn(f"{form3_infos['MachineID']}号设备,量产湿胶视图Smallbatch_perfect无Wet_Glue_lm有效数据")
                    elif pd.isna(get_mp_wet(form3_infos['MoID']).loc[0, 'Wet_Glue_Ra']):
                        flash_result[41] = "量产湿胶视图Smallbatch_perfect无Wet_Glue_Ra有效数据"
                        logger.warn(f"{form3_infos['MachineID']}号设备,量产湿胶视图Smallbatch_perfect无Wet_Glue_Ra有效数据")
                    elif pd.isna(get_mp_wet(form3_infos['MoID']).loc[0, 'Wet_Glue_R9']):
                        flash_result[41] = "量产湿胶视图Smallbatch_perfect无Wet_Glue_R9有效数据"
                        logger.warn(f"{form3_infos['MachineID']}号设备,量产湿胶视图Smallbatch_perfect无Wet_Glue_R9有效数据")
                    else:
                        # 获取点粉电流，电压再上限的基础上再加5V
                        df_volt = get_spec_range(item_code)
                        voltage = (df_volt[df_volt['Parameter'] == 'ForwardVoltage_V']['USL'].to_list()[0]) + 5
                        current = get_item_features(item_code, '点粉电流(mA)').loc[0, 'FEATURE_VALUE']
                        if not current:
                            flash_result[40] = "视图ITEM_CODE_VALUE无点粉电流有效数据,找研发负责人"
                            logger.warn(f"{form3_infos['MachineID']}号设备,视图ITEM_CODE_VALUE无点粉电流有效数据")
                        else:
                            set_voltage_current(voltage, current)
                            # 设置最终系数
                            set_cal_factor(end_coef.loc['最终系数'].to_dict())
                            # 设置中心点
                            wet_range = get_mp_wet(form3_infos['MoID'])
                            set_wet_xy(get_color_stand(item_code), wet_range, '2')
                            # 设置色容差
                            set_mac_bin(get_bin_range(item_code), get_spec_range(item_code), form3_infos['ProcessID'])
                            # 设置湿胶lm Ra R9卡控标准
                            set_wet_range(wet_range)
                            mpstate = True
                #  成品与NM工艺
                elif form3_infos['ProcessID'] == '3':
                    # 获取点粉电流，电压再上限的基础上再加5V
                    df_volt = get_spec_range(item_code)
                    # todo:此处修改 若电压超过100V再加5V
                    if df_volt[df_volt['Parameter'] == 'ForwardVoltage_V']['USL'].to_list()[0] > 50:
                        voltage = (df_volt[df_volt['Parameter'] == 'ForwardVoltage_V']['USL'].to_list()[0]) + 8
                    else:
                        voltage = (df_volt[df_volt['Parameter'] == 'ForwardVoltage_V']['USL'].to_list()[0]) + 5
                    current = get_item_features(item_code, '电流Typ(mA)').loc[0, 'FEATURE_VALUE']
                    if not current:
                        flash_result[40] = "视图ITEM_CODE_VALUE无电流Typ(mA)有效数据,找研发负责人"
                        logger.warn(f"{form3_infos['MachineID']}号设备,视图ITEM_CODE_VALUE无电流Typ(mA)有效数据")
                    elif flag2:
                        flash_result[5] = "转烤坐标缺失，请联系工程部相关人员"
                        logger.warn(f"{form3_infos['MachineID']}号设备,转烤坐标缺失")
                    else:
                        set_voltage_current(voltage, current)
                        # 设置最终系数
                        set_cal_factor(end_coef.loc['最终系数'].to_dict())
                        # 设置色容差,获取分光代码
                        set_mac_bin(get_bin_range(item_code), get_spec_range(item_code), form3_infos['ProcessID'])
                        set_range(get_bin_range(item_code), get_spec_range(item_code), match_if(item_code))

                        # 设置中心点
                        set_color_stand(get_color_stand(item_code), df_color)
                        mpstate = True
                if mpstate:
                    # 确认lotID3，存在+1，不存在初始化为1
                    cursor.execute(
                        f"SELECT MAX(LOTID3) FROM MPvalidation_Info1 WHERE MOID='{form3_infos['MoID']}' "
                        f"AND LotID='{form3_infos['LotID']}'AND LotID2='{form3_infos['LotID2']}';")
                    rows = cursor.fetchall()
                    if rows[0][0] is None:
                        lotID3 = 1
                    else:
                        lotID3 = rows[0][0] + 1
                    # 添加LOTID2到列表
                    form3_infos['LotID3'] = lotID3

                    # 保存数据文件
                    ext = os.path.splitext(form3_infos['temp_path'])[1]
                    upnew_filename = 'caldata-' + form3_infos['MoID'] + '-' + str(form3_infos['LotID']) + '-' \
                                     + str(form3_infos['LotID2']) + '-' + str(lotID3) + ext
                    form3_infos['UploadPath'] = os.path.join(uploadsDir, upnew_filename)
                    shutil.copy(form3_infos['temp_path'], form3_infos['UploadPath'])

                    # 删除字典coef_data,temp_path及临时文件
                    os.remove(form3_infos.pop('temp_path'))
                    form3_infos.pop('coef_data')

                    # 获取标准件的测试时间
                    get_time = f"SELECT StandardPartNO as TestNO,CONVERT(char,TestTime,120) as StandardTime FROM " \
                               f"StandardPartData WHERE " \
                               f"StandardPartID='{form3_infos['StandardPartID']}' AND  DataStatus=1"
                    time_df = pd.read_sql(get_time, engine)
                    test_data = pd.merge(time_df, test_data, on='TestNO')

                    # 测试数据整理并写入数据库
                    pars = ['MoID', 'StandardPartID', 'MachineID', 'UserID', 'LotID', 'LotID2', 'LotID3', 'ProcessID']
                    for i in pars:
                        test_data[i] = form3_infos[i]
                    # 数据写入数据库
                    try:
                        test_data.to_sql('MPvalidation_Data1', engine, if_exists='append', index=None)
                    except Exception as e:
                        logger.error(f"{form3_infos['MachineID']}号设备,{form3_infos['MoID']} -- 最终数据存储错误,{e}")
                        flash_result[50] = f"最终数据存储错误!,问题描述：{e}"
                    else:
                        if '最终系数' not in list(end_coef.index):
                            logger.error(f"{form3_infos['MachineID']}号设备，{form3_infos['MoID']}工单不存在'最终系数'行")
                            flash_result[55] = "最终系数获取失败,请重新校准！"
                        dict_coef_data = end_coef.loc['最终系数'].to_dict()
                        # 查询COF中StandardPartNO最大值的符合条件的数据
                        sql_select = f"SELECT max(StandardPartNO) FROM COF WHERE MoID='{form3_infos['MoID']}' and CF='最终系数' and " \
                                     f"MachineID='{form3_infos['MachineID']}'"
                        row = cursor1.execute(sql_select).fetchall()
                        if row[0][0] is None:
                            No = 1
                        else:
                            No = int(row[0][0]) + 1
                        sql_insert = f"INSERT INTO COF (MoID,CF,ITEM_CODE,StandardPartNO," \
                                     f"dU,dx,dy,dRa,dR9,dlm,dI,MachineID,Updatetime,LotID,DeleteStandard)" \
                                     f"values ('{form3_infos['MoID']}','最终系数','{item_code}','{No}'," \
                                     f"{dict_coef_data['dU']},{dict_coef_data['dx']},{dict_coef_data['dy']}," \
                                     f"{dict_coef_data['dRa']},{dict_coef_data['dR9']},{dict_coef_data['dlm']}," \
                                     f"{dict_coef_data['dI']},'{form3_infos['MachineID']}','{datetime.now()}'," \
                                     f"'{str(form3_infos['LotID']) + '-' + str(form3_infos['LotID2']) + '-' + str(form3_infos['LotID3'])}'," \
                                     f"'{NO_list}')"
                        try:
                            cursor1.execute(sql_insert)
                            conn.commit()
                        except Exception as e:
                            logger.error(f"{form3_infos['MachineID']}号设备,{form3_infos['MoID']} -- 最终系数存储错误,{e}")
                            flash_result[60] = f"最终系数存储错误!问题描述：{e}"
                        else:
                            # 保存配置文件
                            down_filename = '量产' + form3_infos['ProcessID'] + '-' + form3_infos['MoID'] + '-' + \
                                            str(form3_infos['LotID']) + '-' + str(form3_infos['LotID2']) + '-' + str(
                                lotID3) + '.ini'
                            endconf_path = os.path.join(downloadsDir, down_filename)
                            ini_file_save(endconf_path)
                            form3_infos['DownloadPath'] = endconf_path
                            # 插入当前时间
                            form3_infos['UpdateTime'] = str(datetime.now())
                            # 遍历字典
                            kvs = [(k, v) for k, v in form3_infos.items() if v is not None]
                            # 创建sql语句
                            sql_data = f"INSERT INTO MPvalidation_Info1({','.join(i[0] for i in kvs)}) " \
                                       f"VALUES ({','.join(repr(i[1]) for i in kvs)});"
                            try:
                                cursor.execute(sql_data)
                                cnxn.commit()
                            except Exception as e:
                                logger.error(f"{form3_infos['MachineID']}号设备,{form3_infos['MoID']} -- 最终信息存储错误,{e}")
                                flash_result[70] = f"最终信息存储错误!问题描述：{e}"
                            else:
                                flash_result['成功'] = '最终系数生成成功'
                                logger.info(
                                    f"{form3_infos['MachineID']}号,{form3_infos['MoID']}最终系数计算成功-{form3_infos['CoePath']}")
                                cnxn.close()
                                conn.close()
    return flash_result, endconf_path, lotID3
